﻿using API_Service.DBHelper;
using Dapper;
using System;
using System.Data;
using System.Linq;
using System.Reflection;

namespace API_Service.BizLogic.BasicData
{
    public class SKUService : BaseLogicService
    {
        private string connString = "";
        private string _strDB = "yfdb";
        public SKUService()
        {
            connString = SqlCreator.ConnectionStrings[this._strDB];
        }
        //同步ERP SKU信息至MES
        public ExecutionResult<dynamic> SyncSKUMasterDataFromERP()
        {
            var result = new ExecutionResult<dynamic>();
            try
            {
                string _strMaxCreateTime = "", _strMaxUpdateTime = "";
                //获取MES系统中当前产品最后更新时间。
                result = this.GetMaxSKUERPDate();
                if (result.Status)
                {
                    var _tmpDate = result.Datas.AsList()[0];
                    _strMaxCreateTime = String.IsNullOrEmpty(_tmpDate.erp_create_time) ? "19990101" : _tmpDate.erp_create_time;
                    _strMaxUpdateTime = String.IsNullOrEmpty(_tmpDate.erp_update_time) ? "19990101" : _tmpDate.erp_update_time;

                    //获取ERP更新数据
                    result = this.GetERPSKUMasterData(_strMaxCreateTime, _strMaxUpdateTime);
                    if (result.Status)
                    {
                        //循环取数，并更新MES库数据
                        foreach (var sku in result.Datas)
                        {
                            bool _bExists = false;
                            //check material is exists
                            var exeRes = this.GetSKUInfoList(sku.sku_no);
                            if (exeRes.Status)
                            {
                                var skuId = 0;
                                var skuFlag = "Y";
                                if (exeRes.Datas.Length > 0)
                                {
                                    skuId = exeRes.Datas[0].id;
                                    skuFlag = exeRes.Datas[0].is_valid;
                                }

                                if (skuFlag == "N")
                                {
                                    continue;
                                }
                                _bExists = skuId > 0 ? true : false;
                                if (_bExists)
                                {
                                    //update
                                    sku.update_user = "SYSTEM_JOB";
                                    sku.id = (object)skuId;
                                    result = this.UpdateSKUBaseData(sku);
                                }
                                else
                                {
                                    //insert 
                                    sku.create_user = "SYSTEM_JOB";
                                    
                                    result = this.InsertSKUBaseData(sku);
                                }
                            }
                            if (!result.Status)
                            {
                                Logger.WriteError(MethodBase.GetCurrentMethod().Name + "-Sync SKU info error. Msg:" + exeRes.Message);
                                return result;
                            }
                        }
                        result.Status = true;
                        result.Message = "Sync ERP SKU to MES finished and success.";
                    }
                }
                Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "Sync ERP material finished, Msg:" + result.Message);
            }
            catch (Exception ex)
            {
                Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);

                result.Status = false;
                result.Message = ex.Message;
            }

            return result;
        }
        //获取MES SKU清单
        public ExecutionResult<dynamic> GetSKUInfoList(string vSKU_NO = null, string vModel = null, string vSKUName = null, string vIsValid = null)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = @"SELECT [id],[model_name],[sku_name] ,[sku_no] ,[sku_property] ,[unit] ,[description],[spec],[description2] ,[unit] ,[remarks]
                          ,[erp_create_time] ,[erp_update_time] ,[is_valid] ,[create_time] ,[create_user] ,[update_time] ,[update_user]
                      FROM [dbo].[c_sku_base_info_t] WHERE 1=1 ";
                    if (!String.IsNullOrEmpty(vSKU_NO))
                    {
                        sql = sql + " AND [sku_no] = @sku_no";
                    }
                    if (!String.IsNullOrEmpty(vModel))
                    {
                        sql = sql + " AND [model_name] = @model";
                    }
                    if (!String.IsNullOrEmpty(vSKUName))
                    {
                        sql = sql + " AND [sku_name] = @skuName";
                    }
                    if (!String.IsNullOrEmpty(vIsValid))
                    {
                        sql = sql + " AND [is_valid] = @isValid";
                    }

                    result.Datas = conn.Query(sql, new { sku_no = vSKU_NO, model = vModel, skuName = vSKUName, isValid = vIsValid }).ToArray();
                    result.Status = true;
                    result.Message = "查询产品基础信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询产品基础信息异常。信息:" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        //新增SKU信息
        public ExecutionResult<dynamic> AddSKUInfo(dynamic vModel)
        {
            return this.InsertSKUBaseData(vModel);
        }
        //修改SKU信息
        public ExecutionResult<dynamic> UpdateSKUInfo(dynamic vModdel)
        {
            return this.UpdateSKUBaseData(vModdel);
        }
        //删除SKU信息
        public ExecutionResult<dynamic> DelSKUInfo(int vId)
        {
            return this.DelSKUBaseData(vId);
        }
        private ExecutionResult<dynamic> InsertSKUBaseData(dynamic vSkuInfo)
        {
            var result = new ExecutionResult<dynamic>();
            #region SQl and parameters
            string _insertSql = @"INSERT INTO [dbo].[c_sku_base_info_t]([model_name],[sku_name],[sku_no],[sku_property],[unit]
                                                           ,[description],[spec],[description2],[remarks],[erp_create_time],[erp_update_time]
                                                           ,[is_valid],[create_time],[create_user])
                                                     VALUES(@model_name,@sku_name,@sku_no,@sku_property,@unit,@description,@spec,@description2,@remarks,
			                                                @erp_create_time,@erp_update_time,@is_valid,GETDATE(),@create_user)";
            var dbParams = new DynamicParameters();
            dbParams.Add("@model_name", ((string)vSkuInfo.model_name).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_name", ((string)vSkuInfo.sku_name).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_no", ((string)vSkuInfo.sku_no).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_property", ((string)vSkuInfo.sku_property).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@unit", ((string)vSkuInfo.unit).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@description", ((string)vSkuInfo.description).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@spec", ((string)vSkuInfo.spec).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@description2", ((string)vSkuInfo.description2).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", ((string)vSkuInfo.remarks).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_create_time", ((string)vSkuInfo.erp_create_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_update_time", ((string)vSkuInfo.erp_update_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
            dbParams.Add("@create_user", (string)vSkuInfo.create_user, DbType.String, ParameterDirection.Input);

            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    
                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "添加产品信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "添加产品信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + " Msg: " + result.Message + "SKU: " + vSkuInfo.sku_no + "");
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "添加产品信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> GetMaxSKUERPDate()
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "SELECT MAX(erp_create_time) erp_create_time,MAX(erp_update_time) erp_update_time FROM dbo.[c_sku_base_info_t] WHERE is_valid = 'Y'";
                    conn.Open();
                    result.Datas = conn.Query(sql).ToArray();

                    result.Message = "获取SKU数据更新时间成功";
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateSKUBaseData(dynamic vSkuInfo)
        {
            var result = new ExecutionResult<dynamic>();
            #region Sql and parameters
            string _insertSql = @"UPDATE [dbo].[c_sku_base_info_t]
                                                       SET [model_name] = @model_name,[sku_name] = @sku_name,[sku_no]=@sku_no,[sku_property] = @sku_property,[unit] = @unit,[description] = @description,
	                                                       [spec] = @spec,[description2] = @description2,[remarks] = @remarks,[erp_create_time] = @erp_create_time,
	                                                       [erp_update_time] = @erp_update_time,[is_valid] = @is_valid,[update_time] = GETDATE(),[update_user] = @update_user
                                                     WHERE [id] = @id";
            var dbParams = new DynamicParameters();

            dbParams.Add("@id", (Int32)vSkuInfo.id, DbType.Int32, ParameterDirection.Input);
            dbParams.Add("@model_name", ((string)vSkuInfo.model_name).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_name", ((string)vSkuInfo.sku_name).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_no", ((string)vSkuInfo.sku_no).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@sku_property", ((string)vSkuInfo.sku_property).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@unit", ((string)vSkuInfo.unit).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@description", ((string)vSkuInfo.description).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@spec", ((string)vSkuInfo.spec).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@description2", ((string)vSkuInfo.description2).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@remarks", ((string)vSkuInfo.remarks).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_create_time", ((string)vSkuInfo.erp_create_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@erp_update_time", ((string)vSkuInfo.erp_update_time).Trim(), DbType.String, ParameterDirection.Input);
            dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
            dbParams.Add("@update_user", (string)vSkuInfo.update_user, DbType.String, ParameterDirection.Input);

            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新产品信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新产品信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + " Msg: " + result.Message + "SKU: " + vSkuInfo.sku_no + "");
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新产品信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> DelSKUBaseData(int vId)
        {
            var result = new ExecutionResult<dynamic>();

            string _insertSql = @"DELETE FROM [dbo].[c_sku_base_info_t] WHERE[id] = @id";
            var dbParams = new DynamicParameters();
            dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "删除产品信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "删除产品信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除产品信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        public ExecutionResult<dynamic> GetERPSKUMasterData(string vCreateTime, string vUpdateTime)
        {
            var result = new ExecutionResult<dynamic>();
            //链接ERP DB，查询物料信息
            using (IDbConnection conn = SqlCreator.Create(DataBaseType.SqlServer, connString))
            {
                try
                {
                    string sql = @"SELECT TOP 300 MB005 as model_name,MB002 as sku_name,MB001 as sku_no,MB025 as sku_property, MB004 as unit, 
                                                MB002 as description,MB003 as spec,MB009 as desc2,MB028 as remarks,
                                               CREATE_DATE as erp_create_time,MODI_DATE as erp_update_time 
                                             FROM dbo.INVMB WHERE MB109='Y' AND (CREATE_DATE >@CREATE_DATE or MODI_DATE >@UPDATE_DATE) AND MB025 <> 'P' ";

                    result.Datas = conn.Query<dynamic>(sql, new { CREATE_DATE = vCreateTime, UPDATE_DATE = vUpdateTime }).ToArray();

                    result.Status = true;
                    result.Message = "Get ERP SKU master data success.";

                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    result.Status = false;
                    result.Message = ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
    }
}